using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SqlQuerry
/// </summary>
public class DataAdmin
{
    public ConnectDB conn = new ConnectDB();
    #region Quan tri User
    public string InsUser(string UserCode, string UserName, string Password, int LocalID, int GroupID, string Desr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsUser",conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserCode", SqlDbType.VarChar, 20).Value = UserCode;
        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 30).Value = UserName;
        cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = Password;
        cmd.Parameters.Add("@LocalID", SqlDbType.Int).Value = LocalID;
        cmd.Parameters.Add("@GroupID", SqlDbType.Int, 11).Value = GroupID;
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 100).Value = Desr;
        cmd.Parameters.Add("@iErrorCode", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        string insuser = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        conn.Close();
        return insuser;
    }
    public string Edit_User(string UserCode, string UserName, string Descr, int GroupID, int LocalID)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditUser", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserCode", SqlDbType.VarChar, 20).Value = UserCode.Trim();
        cmd.Parameters.Add("@UserName", SqlDbType.NVarChar, 30).Value = UserName.Trim();
        cmd.Parameters.Add("@GroupID", SqlDbType.Int, 11).Value = GroupID;
        cmd.Parameters.Add("@LocalID", SqlDbType.Int).Value = LocalID;
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 100).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        conn.Close();
        string error = cmd.Parameters["@iErrorCode"].Value.ToString();
        return error;
    }
    public string DelUser(string UserCode)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spDelUser", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserCode", SqlDbType.VarChar, 20).Value = UserCode.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteNonQuery();
        string error = cmd.Parameters["@iErrorCode"].Value.ToString();
        return error;
    }
    public string ResetPass(string UserCode)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spResetPass", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@UserCode", SqlDbType.VarChar, 20).Value = UserCode.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = cmd.Parameters["@iErrorCode"].Value.ToString();
        return error;
    }
    #endregion
    #region Quan ly nhom nguoi dung
    public string InsGroup(string GroupName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsGroup", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GroupName", SqlDbType.NVarChar, 50).Value = GroupName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string insGroup = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return insGroup;
    }
    public string EditGroup(string GroupID, string GroupName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditGroup", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 5).Value = GroupID.Trim();
        cmd.Parameters.Add("@GroupName", SqlDbType.NVarChar, 50).Value = GroupName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string insGroup = cmd.Parameters["@iErrorCode"].Value.ToString();
        return insGroup;
    }
    public string DelGroup(string GroupName)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spDelGroup", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GroupID", SqlDbType.VarChar, 5).Value = GroupName.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = cmd.Parameters["@iErrorCode"].Value.ToString();
        return error;
    }
    #endregion
    #region Quan ly don vi
    public string InsLocal(string LocalID, string LocalName, string Phone, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsMDHLocal", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@LocalID", SqlDbType.VarChar, 10).Value = LocalID.Trim();
        cmd.Parameters.Add("@LocalName", SqlDbType.NVarChar, 50).Value = LocalName.Trim();
        cmd.Parameters.Add("Phone", SqlDbType.VarChar, 11).Value = Phone.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string insLocal= System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return insLocal;
    }
    public string EditLocal(int LocalID, string LocalName, string Phone, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditMDHLocal", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@LocalID", SqlDbType.Int, 4).Value = LocalID;
        cmd.Parameters.Add("@LocalName", SqlDbType.NVarChar, 50).Value = LocalName.Trim();
        cmd.Parameters.Add("Phone", SqlDbType.VarChar, 11).Value = Phone.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string DelLocal(int LocalID)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditMDHLocal", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@LocalID", SqlDbType.Int, 4).Value = LocalID;
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    #endregion
    #region Quan ly cac tram
    public string InsStations(string StationName, string LocalName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsMDHStations", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@StationName", SqlDbType.NVarChar, 50).Value = StationName.Trim();
        cmd.Parameters.Add("@LocalName", SqlDbType.NVarChar, 50).Value = LocalName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string EditStations(string StationID, string StationName, string LocalName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditMDHStations", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@StationID", SqlDbType.VarChar, 6).Value = StationID.Trim();
        cmd.Parameters.Add("@StationName", SqlDbType.NVarChar, 50).Value = StationName.Trim();
        cmd.Parameters.Add("@LocalName", SqlDbType.NVarChar, 50).Value = LocalName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string DelStations(string StationID)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spDelMDHStations", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@StationID", SqlDbType.VarChar, 6).Value = StationID.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    #endregion
    #region Quan ly may no
    public string Insenerator(string GeneratorName, string Norms, string StationID, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsGenerator", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GeneratorName", SqlDbType.NVarChar, 50).Value = GeneratorName.Trim();
        cmd.Parameters.Add("@Norms", SqlDbType.NVarChar, 50).Value = Norms.Trim();
        cmd.Parameters.Add("@StationID", SqlDbType.NVarChar, 50).Value = StationID.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string EditGenerator(string GeneratorName, string Norms, string StationID, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditGenerator", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GeneratorName", SqlDbType.NVarChar, 50).Value = GeneratorName.Trim();
        cmd.Parameters.Add("@Norms", SqlDbType.NVarChar, 50).Value = Norms.Trim();
        cmd.Parameters.Add("@StationID", SqlDbType.NVarChar, 50).Value = StationID.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string DelGenerator(int GeneratorID)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spDelGenerator", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@GeneratorID", SqlDbType.NVarChar, 50).Value = GeneratorID;
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    #endregion
    #region Quan ly dich vu
    public string InsService(string ServiceName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spInsService", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ServiceName", SqlDbType.NVarChar, 50).Value = ServiceName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string EditService(int ServiceID, string ServiceName, string Descr)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spEditService", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value = ServiceID;
        cmd.Parameters.Add("@ServiceName", SqlDbType.NVarChar, 50).Value = ServiceName.Trim();
        cmd.Parameters.Add("@Descr", SqlDbType.NVarChar, 50).Value = Descr.Trim();
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    public string EditService(int ServiceID)
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("dbo.spDelService", conn.GetConnect());
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@ServiceID", SqlDbType.Int, 4).Value = ServiceID;
        cmd.Parameters.Add("@iErrorCode", SqlDbType.VarChar, 1).Direction = ParameterDirection.Output;
        cmd.ExecuteScalar();
        conn.Close();
        string error = System.Convert.ToString(cmd.Parameters["@iErrorCode"].Value);
        return error;
    }
    #endregion
    public DataSet GetDataSet(string sql)
    {
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn.GetConnect());
        da.Fill(ds);
        da.Dispose();
        conn.Close();
        return ds;
    }
}
